package br.com.vti.sisp_sicrim.antigo.dao;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.UUID;

import br.com.vti.sisp_sicrim.antigo.dao.util.HibernateUtil;

public class SicEstatisticaDAO {

	private int codigoRetorno = 0; // *ERROR
	private String msgRetorno = ""; // *ERROR
	public long qtdRegistros = 0; // *counter
	public long qtdQuantTrans = 0; // *counter
	public long nroRegistro = 0; // *counter
	public long nroQuantTrans = 0;
	private String[] listacol = { "", "CodigoModelo", "CodigoTipo", "DataAtualiz", "MatriculaFunc", "NumeroSeq", "Horaem", "NumeroPosto", "Ufeder", "NumeroRg", "NomeId" };
	private ResultSet query = null;
	private ResultSet queryQuantTrans = null;

	public String insert(String pCodigoModelo, Integer pCodigoTipo, Integer pDataAtualiz, Integer pMatriculaFunc, Integer pNumeroSeq, Integer pHoraem, Integer pNumeroPosto, String pUfeder, Long pNumeroRg, String pNomeId) {

		String sql = "{ ? = call sp_grava_Sic_Estatistica ( ?,?,?,?,?,?,?,?,?,? ) }";
		UUID pId;
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {

			CallableStatement stp = HibernateUtil.getConnection().prepareCall(sql);
			stp.registerOutParameter(1, Types.OTHER);
			stp.setString(2, pCodigoModelo); // (getColuna("CodigoModelo")
			stp.setInt(3, pCodigoTipo); // (getColuna("CodigoTipo")
			stp.setInt(4, pDataAtualiz); // (getColuna("DataAtualiz")
			stp.setInt(5, pMatriculaFunc); // (getColuna("MatriculaFunc")
			stp.setInt(6, pNumeroSeq); // (getColuna("NumeroSeq")
			stp.setInt(7, pHoraem); // (getColuna("Horaem")
			stp.setInt(8, pNumeroPosto); // (getColuna("NumeroPosto")
			stp.setString(9, pUfeder); // (getColuna("Ufeder")
			stp.setLong(10, pNumeroRg); // (getColuna("NumeroRg")
			stp.setString(11, pNomeId); // (getColuna("NomeId")
			// execute SP
			stp.execute();
			pId = (UUID) stp.getObject(1);
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
			return null;
		}
		return String.valueOf(pId);
	}

	public void insertQuantTrans(String pId, int pSeq, Long pQuantTrans) {
		String sql = "{ call sp_grava_Sic_EstatisticaQuantTrans ( ?, ?, ? ) }";
		try {

			CallableStatement stp = HibernateUtil.getConnection().prepareCall(sql);
			stp.setObject(1, UUID.fromString(pId)); // getColuna("Id")
			stp.setInt(2, pSeq); // getColuna("Seq")
			stp.setLong(3, pQuantTrans); // getColuna("QuantTrans")
			stp.execute();
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

	}

	public void update(String pId, String pCodigoModelo, Integer pCodigoTipo, Integer pDataAtualiz, Integer pMatriculaFunc, Integer pNumeroSeq, Integer pHoraem, Integer pNumeroPosto, String pUfeder, Long pNumeroRg, String pNomeId) {

		String sql = "{ call sp_atualiza_Sic_Estatistica ( ?,?,?,?,?,?,?,?,?,?,? ) }";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {

			CallableStatement stp = HibernateUtil.getConnection().prepareCall(sql);
			stp.setObject(1, UUID.fromString(pId)); // getColuna("Id")
			stp.setString(2, pCodigoModelo); // getColuna("CodigoModelo")
			stp.setInt(3, pCodigoTipo); // getColuna("CodigoTipo")
			stp.setInt(4, pDataAtualiz); // getColuna("DataAtualiz")
			stp.setInt(5, pMatriculaFunc); // getColuna("MatriculaFunc")
			stp.setInt(6, pNumeroSeq); // getColuna("NumeroSeq")
			stp.setInt(7, pHoraem); // getColuna("Horaem")
			stp.setInt(8, pNumeroPosto); // getColuna("NumeroPosto")
			stp.setString(9, pUfeder); // getColuna("Ufeder")
			stp.setLong(10, pNumeroRg); // getColuna("NumeroRg")
			stp.setString(11, pNomeId); // getColuna("NomeId")
			// execute SP
			stp.execute();
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}
	}

	public void updateQuantTrans(String pId, int pSeq, Long pQuantTrans) {
		String sql = "{ call sp_atualiza_Sic_EstatisticaQuantTrans ( ?, ?, ? ) }";
		try {

			CallableStatement stp = HibernateUtil.getConnection().prepareCall(sql);
			stp.setObject(1, UUID.fromString(pId)); // getColuna("Id")
			stp.setInt(2, pSeq); // getColuna("Seq")
			stp.setLong(3, pQuantTrans); // getColuna("QuantTrans")
			stp.execute();
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

	}

	public ResultSet selectMotida(String pCodigoModelo, Integer pCodigoTipo, Integer pDataAtualiz, Integer pMatriculaFunc) {

		String sql = "select * from sp_consMotida_Sic_Estatistica (";
		sql = sql + "pCodigoModelo := '" + pCodigoModelo + "',";
		sql = sql + "pCodigoTipo := '" + pCodigoTipo + "',";
		sql = sql + "pDataAtualiz := '" + pDataAtualiz + "',";
		sql = sql + "pMatriculaFunc := '" + pMatriculaFunc + "' ";
		sql = sql + ")";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {
			query = HibernateUtil.executarQuery(sql);
			if (query == null) {
				return null;
			} else {
				query.first();
				if (query.getRow() == 0) {
					query = null;
					return query;
				}
				if (query.first()) {
					query.last();
					qtdRegistros = query.getRow();
					nroRegistro = 1;
					query.first();
				}
			}
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

		return query;
	}

	public ResultSet readMotida() {

		String sql = "select * from sp_leMotida_Sic_Estatistica ()";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {
			query = HibernateUtil.executarQuery(sql);
			if (query == null) {
				return null;
			} else {
				query.first();
				if (query.getRow() == 0) {
					query = null;
					return query;
				}
				if (query.first()) {
					query.last();
					qtdRegistros = query.getRow();
					nroRegistro = 1;
					query.first();
				}
			}
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

		return query;
	}

	public ResultSet findMotidain(String pCodigoModelo, Integer pCodigoTipo, Integer pMatriculaFunc, Integer pDataAtualizIni, Integer pDataAtualizFim) {

		String sql = "select * from sp_pesqMotidaFaixa_Sic_Estatistica (";
		sql = sql + "pCodigoModelo := '" + pCodigoModelo + "',";
		sql = sql + "pCodigoTipo := '" + pCodigoTipo + "',";
		sql = sql + "pMatriculaFunc := '" + pMatriculaFunc + "',";
		sql = sql + "pDataAtualizIni := '" + pDataAtualizIni + "',";
		sql = sql + "pDataAtualizFim := '" + pDataAtualizFim + "' ";
		sql = sql + ")";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {
			query = HibernateUtil.executarQuery(sql);
			if (query == null) {
				return null;
			} else {
				query.first();
				if (query.getRow() == 0) {
					query = null;
					return query;
				}
				if (query.first()) {
					query.last();
					qtdRegistros = query.getRow();
					nroRegistro = 1;
					query.first();
				}
			}
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

		return query;
	}

	public ResultSet pesquisaProximo() {
		// query
		try {
			if (query == null)
				return null;
			if (query.getRow() == 0) {
				query = null;
				return null;
			}
			query.next();
			if (query.getRow() == 0) {
				query = null;
				return null;
			}
			if (query != null) {
				nroRegistro++;
			}
			return query;
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
			return null;
		}
	}

	public boolean temProximo() {
		try {
			if (query == null)
				return false;
			if (query.getRow() == 0) {
				return false;
			}
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
			return false;
		}
		return (nroRegistro < qtdRegistros);
	}

	public void fecha() {
		if (query == null)
			return;
		try {
			query.close();
			query = null;
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
			query = null;
		}
	}

	public ResultSet pesquisaQuantTrans(String id) {

		String sql = "select * from sp_pesquisa_QuantTrans_Sic_Estatistica ( pId := '" + id + "' )";
		try {
			queryQuantTrans = HibernateUtil.executarQuery(sql);

			if (queryQuantTrans == null) {
				return null;
			} else {
				queryQuantTrans.first();
				if (queryQuantTrans.getRow() == 0) {
					queryQuantTrans = null;
					return queryQuantTrans;
				}
				if (queryQuantTrans.first()) {
					queryQuantTrans.last();
					qtdQuantTrans = queryQuantTrans.getRow();
					nroQuantTrans = 1;
					queryQuantTrans.first();
				}
			}

		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

		return queryQuantTrans;
	}

	/*
	 * public ResultSet proximoQuantTrans() {
	 * 
	 * if (queryQuantTrans == null) { return null; } try { queryQuantTrans.next(); } catch (SQLException e) { codigoRetorno = 999; msgRetorno = e.getMessage(); return null; }
	 * 
	 * return queryQuantTrans; }
	 */

	public ResultSet proximoQuantTrans() {
		try {
			if (queryQuantTrans == null)
				return null;
			if (queryQuantTrans.getRow() == 0) {
				queryQuantTrans = null;
				return null;
			}
			queryQuantTrans.next();
			if (queryQuantTrans.getRow() == 0) {
				queryQuantTrans = null;
				return null;
			}
			if (queryQuantTrans != null) {
				nroQuantTrans++;
			}
			return queryQuantTrans;
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
			return null;
		}
	}

	public int getColuna(String coluna) {
		for (int i = 0; i < listacol.length; i++) {
			if (listacol[i].equals(coluna))
				return i;
		}
		return -1;
	}

}
